/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * @author zhouqh
 * Date: 2010-5-24 下午02:15:11
 */
package com.ccxe.manage.db;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.MissingResourceException;
import java.util.ResourceBundle;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author zhouqh
 * 
 */
public abstract class CcxDBCommon implements CcxDB {

	/** Database connection */
	private Connection dbConn;

	private static final Logger logger = LoggerFactory.getLogger(CcxDBCommon.class);
	
	private ResourceBundle dbBundle;

	@SuppressWarnings("unused")
	private ResourceBundle sqlBundle;

	/** 用来保存产生的Statement */
	private Hashtable<String, Statement> statements = new Hashtable<String, Statement>();

	private final static String DB_PROPERTIES_FILE = "config/CCXDB";
	//private final static String DB_PROPERTIES_FILE = "db";

	/**
	 * 存放sql语句的文件名前缀
	 */
	//private final static String SQL_PROPERTIES_FILE_PREFIX = "config/CPHSQL-";
	
	@SuppressWarnings("unused")
	private static String SQL_PROPERTIES_FILE = null;

	/**
	 * 是否使用数据源
	 */
	private boolean useDataSource = false;

	private final static String USE_DATASOURCE = "useDataSource";

	@SuppressWarnings("unused")
	private final static String DATASOURCE_NAME_KEY = "DataSource.name";

	private final static String DRIVER_KEY = "driver";

	private final static String URL_KEY = "url";

	private final static String USERNAME_KEY = "username";

	private final static String PWD_KEY = "password";

	public CcxDBCommon() throws Exception {
		dbConn = null;
		try {
			initialize();
			connect();
		} catch(Exception e) {
			logger.info("初始化数据库失败！",e);
			throw e;
		}
	}

	/**
	 * Initialize the resource bundle
	 * 
	 * @throws Exception
	 */
	private void initialize() throws Exception {
		try {
			// Load the database DataSource config file if it hasn't already.
			if (dbBundle == null) {
				dbBundle = ResourceBundle.getBundle(DB_PROPERTIES_FILE);
			}
		} catch (MissingResourceException e) {
			throw new Exception(e);
		}
	}

	/* 
	 * Implementation.
	 */
	public Connection getConnection() throws Exception {
		return dbConn;
	}

	/**
	 * Connects to the database using the JDBC/DataSource Config specified in
	 * the properties file (config/CCXDB.properties) passed when the class was
	 * constructed.
	 * 
	 * @throws Exception
	 */
	private void connect() throws Exception {

		useDataSource = Boolean
				.parseBoolean(dbBundle.getString(USE_DATASOURCE));

		// 使用数据源
		if (useDataSource) {
			
		} else {
			// 使用JDBC直接连
			String driver = dbBundle.getString(DRIVER_KEY);
			String url = dbBundle.getString(URL_KEY);
			String user = dbBundle.getString(USERNAME_KEY);
			String password = dbBundle.getString(PWD_KEY);

			try {
				Class.forName(driver);
				Connection conn = DriverManager.getConnection(url, user,
						password);
				this.dbConn = conn;
			} catch (ClassNotFoundException e) {
				throw new Exception(e);
			} catch (SQLException e) {
				throw new Exception(e);
			}
		}
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see cn.com.clear2pay.epayments.cph.db.PaymentDB#close()
	 */
	public void close() throws Exception {
		Iterator<Statement> it = statements.values().iterator();
		try {
			while (it.hasNext()) {
				Statement stmt = (Statement) it.next();
				stmt.close();
			}
			statements.clear();
//			if(dbConn != null)
//				if(dbconnPool != null && dbconnPool.getPoolName().equalsIgnoreCase("dbpool.CORPOOL"))
//					dbconnPool.returnConnection(dbConn);
//				else
					dbConn.close();
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	/*
	 * Implementation.
	 */
	public void close(String sqlKey) throws Exception {
		if (statements.containsKey(sqlKey)) {
			Statement stmt = (Statement) statements.remove(sqlKey);
			try {
				stmt.close();
			} catch (SQLException e) {
				throw new Exception(e);
			}
		}

	}

	/*
	 * Implementation.
	 */
	public void commit() throws Exception {
		try {
			dbConn.commit();
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	/*
	 * Implementation.
	 */
	public void rollback() throws Exception {
		try {
			dbConn.rollback();
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	/*
	 * Implementation.
	 */
	public void setAutoCommit(boolean mode) throws Exception {
		try {
			if (dbConn.getAutoCommit() != mode) {
				dbConn.setAutoCommit(mode);
			}
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	/*
	 * Implementation.
	 */
	public ResultSet executeQuery(String sqlKey) throws Exception {
		return executeQuery(sqlKey, null);
	}

	/*
	 * Implementation.
	 */
	public ResultSet executeQuery(String sqlKey, Object[] sqlParameters)
			throws Exception {
		try {
			PreparedStatement pstmt = prepareStatement(sqlKey, sqlParameters);
			return pstmt.executeQuery();
		} catch (SQLException e) {
			throw new Exception(e);
		} finally {
			// 不能在此关闭Connection.ResultSet对象与PreparedStatement相关�?
			// 如关闭Connection,ResultSet对象会被关闭
			// close();
		}
	}

	/*
	 * Implementation.
	 */
	public int executeUpdate(String sqlKey, Object[] sqlParameters)
			throws Exception {
		try {
			PreparedStatement pstmt = prepareStatement(sqlKey, sqlParameters);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	/**
	 * Prepare the statement for execution.
	 * 
	 * @throws SQLException
	 */
	private PreparedStatement prepareStatement(String sqlKey,
			Object[] sqlParameters) throws Exception, SQLException {

		// Get the specified query
		String sqlQuery = null;

		try {
			// sqlQuery = sqlBundle.getString(sqlKey);
			sqlQuery = sqlKey;
		} catch (MissingResourceException e) {
			// query not found in resource bundle

			throw new Exception(e);
		}

		PreparedStatement pstmt;

		// Create a Statement

		if (statements.containsKey(sqlKey)) {
			pstmt = (PreparedStatement) statements.get(sqlKey);
			pstmt.clearParameters();
		} else {
			pstmt = dbConn.prepareStatement(sqlQuery);
			statements.put(sqlKey, pstmt);
		}

		// Process the variable parameters
		for (int i = 0; sqlParameters != null && i < sqlParameters.length; i++) {
			if (sqlParameters[i] == null) {
				// Parameter at index {0} was null.
				throw new NullPointerException(sqlKey+"\n传入的参数为空！");
			}
			String className = sqlParameters[i].getClass().getName();
			if (className.equals("java.lang.String")) {
				pstmt.setString((i + 1), (String) sqlParameters[i]);
			} else if (className.equals("java.lang.Integer")) {
				Integer j = (Integer) sqlParameters[i];
				pstmt.setInt((i + 1), j.intValue());
			} else if (className.equals("java.lang.Long")) {
				Long j = (Long) sqlParameters[i];
				pstmt.setLong((i + 1), j.longValue());
			} else if (className.equals("java.lang.Byte")) {
				Byte j = (Byte) sqlParameters[i];
				pstmt.setByte((i + 1), j.byteValue());
			} else if (className.equals("java.lang.Short")) {
				Short j = (Short) sqlParameters[i];
				pstmt.setShort((i + 1), j.shortValue());
			} else if (className.equals("java.lang.Double")) {
				Double j = (Double) sqlParameters[i];
				pstmt.setDouble((i + 1), j.doubleValue());
			} else if (className.equals("java.lang.Float")) {
				Float j = (Float) sqlParameters[i];
				pstmt.setFloat((i + 1), j.floatValue());
			} else if (className.equals("java.lang.Boolean")) {
				Boolean j = (Boolean) sqlParameters[i];
				pstmt.setBoolean((i + 1), j.booleanValue());
			} else if (className.equals("java.lang.Character")) {
				Character j = (Character) sqlParameters[i];
				pstmt.setString((i + 1), j.toString());
				/*
				 * This next statement MUST COME BEFORE the java.sql.Timestamp
				 * test. Sybase returns a com.sybase.jdbc2.tds.SybTimestamp
				 * (which extends the standard java.sql.Timestampo class), when
				 * the data is retrieved from the database. However doing an
				 * insert with the object fails with:
				 * java.lang.ClassNotFoundException
				 * :com.sybase.jdbc2.tds.SybTimestamp Aaggghhh. Silly tossers.
				 * So we'll look for it here, and recreate a basic
				 * java.sql.Timestamp field. The things we have to do.
				 */
			} else if (className.equals("com.sybase.jdbc2.tds.SybTimestamp")) {
				Timestamp basicTimestamp = new Timestamp(
						((Timestamp) sqlParameters[i]).getTime());
				basicTimestamp.setNanos(((Timestamp) sqlParameters[i])
						.getNanos());
				pstmt.setTimestamp((i + 1), basicTimestamp);
			} else if (className.equals("java.sql.Date")) {
				pstmt.setDate((i + 1), (Date) sqlParameters[i]);
			} else if (className.equals("java.sql.Timestamp")) {
				pstmt.setTimestamp((i + 1), (Timestamp) sqlParameters[i]);
			} else if (className.equals("java.math.BigDecimal")) {
				pstmt.setBigDecimal((i + 1), (BigDecimal) sqlParameters[i]);
			}else if (className
					.equals("com.ccx.indexfuture.db.Null")) {
				pstmt.setNull((i + 1), ((Null) sqlParameters[i]).getDataType());
			} else {
				// Unsupported Parameter type : {0}.
				throw new Exception("数据库异常！");
			}
		}

		return pstmt;
	}

	/*
	 * Implementation.
	 */
	public int getSearchTotalRowSize(String sql) throws Exception {
		int resultListSize = 0;
logger.info("getSearchTotalRowSize SQL : "+sql);
		try {
			Statement stmt = createStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				resultListSize = rs.getInt(1);
//logger.info("getSearchTotalRowSize : "+resultListSize);				
			}
		} catch (SQLException e) {
			throw new Exception(e);
		} finally {
			close(sql);
		}
		return resultListSize;
	}

	/*
	 * Implementation.
	 */
	public int getSearchTotalRowSize(String countSqlKey, Object[] sqlParameters)
			throws Exception {
		int resultListSize = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = prepareStatement(countSqlKey, sqlParameters);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				resultListSize = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new Exception(e);
		} finally {
			close(countSqlKey);
		}

		return resultListSize;
	}

	/**
	 * 获得可以移动游标�?code>Statement</code>
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	private Statement createStatement(String sql) throws Exception {
		Statement stmt = null;
		try {
			if (statements.containsKey(sql)) {
				stmt = (Statement) statements.get(sql);
			} else {
				stmt = dbConn.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				statements.put(sql, stmt);
			}
		} catch (SQLException e) {
			throw new Exception(e);
		}
		return stmt;
	}

	private String getPageingSql(String sql, int pageCount, int limit) {
		int start = (pageCount - 1) * limit + 1;
		int end = pageCount * limit;
		StringBuffer buffer = new StringBuffer();
		buffer.append(
				"select fin.* from (select media.*,  rownum row_num from(")
				.append(sql).append(") media where rownum<").append(end + 1)
				.append(") fin where row_num>").append(start - 1);
		String finalSql = buffer.toString();
		return finalSql;
	}

	public ResultSet executeQuery(String sql, int pageCount, int limit)
			throws Exception {
		String finalSql = getPageingSql(sql, pageCount, limit);
		return executeQuery(finalSql);
	}

	/*
	 * 由于跟数据库类型有关�?��未实现，由针对各种数据库的实现类里实现此方法
	 */
	public abstract long getNextAddressNumber() throws Exception;

	/*
	 * 由于跟数据库类型有关�?��未实现，由针对各种数据库的实现类里实现此方法
	 */
	public abstract long getNextMsgNumber() throws Exception;

}
